﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Zyg.Utility.DBUtility;
using Zyg.SSO.Core.Business;

namespace Zyg.SSO.Core.Providers.DALProviders
{
    public partial class MySqlProvider : DALProvider
    {
        #region Sql Statements

        private static readonly string SqlSelectAppById = "SELECT `ID`,`Name`,`Url`,`IP`,`Port`,`DateCreated`,`PictureAddress`,`LoginFile` FROM `App` WHERE `ID` = @ID; set names utf8;";
        private static readonly string SqlSelectAppsAll = "SELECT `ID`,`Name`,`Url`,`IP`,`Port`,`DateCreated`,`PictureAddress`,`LoginFile` FROM `App`; set names utf8;";

        private static readonly string SqlInsertApp = "INSERT INTO `App` (`ID`,`Name`,`Url`,`IP`,`Port`,`DateCreated`,`PictureAddress`,`LoginFile`) VALUES (@ID,@Name,@Url,@IP,@Port,@DateCreated,@PictureAddress,@LoginFile); set names utf8;";

        private static readonly string SqlUpdateAppById = "UPDATE `App` SET `Name` = @Name,`Url` = @Url,`IP` = @IP,`Port` = @Port,`DateCreated` = @DateCreated,`PictureAddress` = @PictureAddress,`LoginFile` = @LoginFile WHERE `ID` = @ID; set names utf8;";

        private static readonly string SqlDeleteAppById = "DELETE FROM `App` WHERE `ID` = @ID; set names utf8;";

        #endregion

        public override Zyg.SSO.Core.Business.App SelectApp(Guid id)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, id);
            MySqlDataReader reader = db.ExecuteSqlReader(SqlSelectAppById);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Business.App app = new App();

                if (!reader.IsDBNull(1)) app.Name = reader.GetString(1);
                if (!reader.IsDBNull(2)) app.Url = reader.GetString(2);
                if (!reader.IsDBNull(3)) app.IP = reader.GetString(3);
                if (!reader.IsDBNull(4)) app.Port = reader.GetInt32(4);
                if (!reader.IsDBNull(5)) app.DateCreated = reader.GetDateTime(5);
                if (!reader.IsDBNull(6)) app.PictureAddress = reader.GetString(6);
                if (!reader.IsDBNull(7)) app.LoginFile = reader.GetString(7);

                reader.Close();

                return app;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public override void InsertApp(Zyg.SSO.Core.Business.App app)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, app.Id);
            db.AddParameter("@Name", MySqlDbType.VarChar, app.Name);
            db.AddParameter("@Url", MySqlDbType.VarChar, app.Url);
            db.AddParameter("@IP", MySqlDbType.VarChar, app.IP);
            db.AddParameter("@Port", MySqlDbType.Int32, app.Port);
            db.AddParameter("@DateCreated", MySqlDbType.DateTime, app.DateCreated);
            db.AddParameter("@PictureAddress", MySqlDbType.VarChar, app.PictureAddress);
            db.AddParameter("@LoginFile", MySqlDbType.VarChar, app.LoginFile);

            db.ExecuteSql(SqlInsertApp);
        }

        public override void DeleteApp(Zyg.SSO.Core.Business.App app)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, app.Id);

            db.ExecuteSql(SqlDeleteAppById);
        }

        public override void UpdateApp(Zyg.SSO.Core.Business.App app)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, app.Id);
            db.AddParameter("@Name", MySqlDbType.VarChar, app.Name);
            db.AddParameter("@Url", MySqlDbType.VarChar, app.Url);
            db.AddParameter("@IP", MySqlDbType.VarChar, app.IP);
            db.AddParameter("@Port", MySqlDbType.Int32, app.Port);
            db.AddParameter("@DateCreated", MySqlDbType.DateTime, app.DateCreated);
            db.AddParameter("@PictureAddress", MySqlDbType.VarChar, app.PictureAddress);
            db.AddParameter("@LoginFile", MySqlDbType.VarChar, app.LoginFile);

            db.ExecuteSql(SqlUpdateAppById);
        }

        public override IList<Zyg.SSO.Core.Business.App> FillApps()
        {
            IList<App> results = new List<App>();

            MySqlUtility db = new MySqlUtility();

            MySqlDataReader reader = db.ExecuteSqlReader(SqlSelectAppsAll);

            if (reader != null)
            {
                while (reader.Read())
                {
                    App app = new App();

                    if (!reader.IsDBNull(0)) app.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) app.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) app.Url = reader.GetString(2);
                    if (!reader.IsDBNull(3)) app.IP = reader.GetString(3);
                    if (!reader.IsDBNull(4)) app.Port = reader.GetInt32(4);
                    if (!reader.IsDBNull(5)) app.DateCreated = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6)) app.PictureAddress = reader.GetString(6);
                    if (!reader.IsDBNull(7)) app.LoginFile = reader.GetString(7);

                    app.MarkOld();

                    results.Add(app);
                }

                reader.Close();
            }

            return results;
        }
    }
}
